﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Data.Linq;
using Vit.DataAccess;
using Vit.SchemaExplorer;
using Vit.Common.Extensions;

namespace Vit.SchemaExplorer.Sql
{
    public class SqlSchemaProvider : IDatabaseSchemaProvider
    {
        Database db;

        public SqlSchemaProvider(string connectionString)
        {
            db = DatabaseFactory.Create(connectionString, "System.Data.SqlClient");
            GetDatabaseVersion();
        }

        private void GetDatabaseVersion()
        {
            var cmd = db.GetSqlStringCommand(DatabaseVersionSqlString);
            cmd.Connection = db.CreateConnection();
            cmd.Connection.Open();
            DatabaseVersion = cmd.ExecuteScalar().ConvertTo<string>(string.Empty);
        }

        #region IDatabaseSchemaProvider Members

        public IEnumerable<ColumnSchemaInfo> GetColumnInfo()
        {
            SqlStringAccessor<ColumnSchemaInfo> da = new SqlStringAccessor<ColumnSchemaInfo>(db, this.ColumnObjectInfoSqlString, new ColumnSchemaInfoRowMapper());
            return da.Execute();
        }

        public IEnumerable<ParameterSchemaInfo> GetParameterInfo()
        {
            SqlStringAccessor<ParameterSchemaInfo> da = new SqlStringAccessor<ParameterSchemaInfo>(db, this.ParameterObjectInfoSqlString, new ParameterSchemaInfoRowMapper());
            return da.Execute();
        }

        public DbType ToDbType(string nativeType)
        {
            DbType dataType = DbType.String;
            #region SqlServer
            switch (nativeType.Trim().ToLower())
            {
                case "bigint":
                    dataType = DbType.Int64;
                    break;
                case "binary":
                    dataType = DbType.Binary;
                    break;
                case "bit":
                    dataType = DbType.Boolean;
                    break;
                case "char":
                    dataType = DbType.StringFixedLength;
                    break;
                case "date":
                    dataType = DbType.Date;
                    break;
                case "datetime":
                    dataType = DbType.DateTime;
                    break;
                case "datetime2":
                    dataType = DbType.DateTime2;
                    break;
                case "datetimeoffset":
                    dataType = DbType.DateTimeOffset;
                    break;
                case "decimal":
                    dataType = DbType.Decimal;
                    break;
                case "float":
                    dataType = DbType.Double;
                    break;
                case "image":
                    dataType = DbType.Binary;
                    break;
                case "int":
                    dataType = DbType.Int32;
                    break;
                case "money":
                    dataType = DbType.Currency;
                    break;
                case "nchar":
                    dataType = DbType.StringFixedLength;
                    break;
                case "ntext":
                    dataType = DbType.String;
                    break;
                case "numeric":
                    dataType = DbType.VarNumeric;
                    break;
                case "nvarchar":
                    dataType = DbType.String;
                    break;
                case "real":
                    dataType = DbType.Double;
                    break; ;
                case "smalldatetime":
                    dataType = DbType.Date;
                    break;
                case "smallint":
                    dataType = DbType.Int16;
                    break;
                case "smallmoney":
                    dataType = DbType.Currency;
                    break;
                case "sql_variant":
                    dataType = DbType.Object;
                    break; ;
                case "text":
                    dataType = DbType.String;
                    break;
                case "time":
                    dataType = DbType.Time;
                    break;
                case "tinyint":
                    dataType = DbType.Int16;
                    break; ;
                case "uniqueidentifier":
                    dataType = DbType.Guid;
                    break; ;
                case "varbinary":
                    dataType = DbType.Binary;
                    break; ;
                case "varchar":
                    dataType = DbType.String;
                    break; ;
                case "xml":
                    dataType = DbType.Xml;
                    break; ;
                default:
                    dataType = DbType.String;
                    break;
            }

            #endregion
            return dataType;
        }

        public Type ToSystemType(string nativeType, bool nullable)
        {
            Type dataType = null;
            #region SqlServer
            switch (nativeType.Trim().ToLower())
            {
                case "bigint":
                    dataType = nullable ? typeof(long?) : typeof(long);
                    break;
                case "binary":
                    dataType = typeof(Binary);
                    break;
                case "bit":
                    dataType = nullable ? typeof(bool?) : typeof(bool);
                    break;
                case "char":
                    dataType = typeof(string);
                    break;
                case "date":
                case "datetime":
                case "datetime2":
                    dataType = nullable ? typeof(DateTime?) : typeof(DateTime);
                    break;
                case "datetimeoffset":
                    dataType = nullable ? typeof(DateTimeOffset?) : typeof(DateTimeOffset);
                    break;
                case "decimal":
                    dataType = nullable ? typeof(decimal?) : typeof(decimal);
                    break;
                case "float":
                    dataType = nullable ? typeof(double?) : typeof(double);
                    break;
                case "image":
                    dataType = typeof(Binary);
                    break;
                case "int":
                    dataType = nullable ? typeof(int?) : typeof(int);
                    break;
                case "money":
                    dataType = nullable ? typeof(decimal?) : typeof(decimal);
                    break;
                case "nchar":
                    dataType = typeof(string);
                    break;
                case "ntext":
                    dataType = typeof(string);
                    break; ;
                case "numeric":
                    dataType = nullable ? typeof(decimal?) : typeof(decimal);
                    break; ;
                case "nvarchar":
                    dataType = typeof(string);
                    break; ;
                case "real":
                    dataType = nullable ? typeof(float?) : typeof(float);
                    break; ;
                case "smalldatetime":
                    dataType = nullable ? typeof(DateTime?) : typeof(DateTime);
                    break; ;
                case "smallint":
                    dataType = nullable ? typeof(short?) : typeof(short);
                    break; ;
                case "smallmoney":
                    dataType = nullable ? typeof(decimal?) : typeof(decimal);
                    break; ;
                case "sql_variant":
                    dataType = typeof(object);
                    break; ;
                case "text":
                    dataType = typeof(string);
                    break;
                case "time":
                    dataType = nullable ? typeof(TimeSpan?) : typeof(TimeSpan);
                    break; ;
                case "timestamp":
                    dataType = typeof(Binary);
                    break; ;
                case "tinyint":
                    dataType = nullable ? typeof(byte?) : typeof(byte);
                    break;
                case "uniqueidentifier":
                    dataType = nullable ? typeof(Guid?) : typeof(Guid);
                    break; ;
                case "varbinary":
                    dataType = typeof(Binary);
                    break; ;
                case "varchar":
                    dataType = typeof(string);
                    break;
                case "xml":
                    dataType = typeof(System.Xml.Linq.XElement);
                    break;
                default:
                    dataType = typeof(string);
                    break;
            }

            #endregion
            return dataType;
        }

        public object ConvertValueType(string nativeType, object value)
        {
            if (value == null || string.IsNullOrEmpty(value.ToString()))
                return DBNull.Value;
            switch (nativeType.Trim().ToLower())
            {
                case "uniqueidentifier":
                    return new Guid(value.ToString());
            }
            return value;
        }

        public string BuildFullName(string shcema, string owner, string name)
        {
            return string.Format("[{0}].[{1}]", shcema, name);
        }

        public string BuildSafeName(string name)
        {
            return string.Format("[{0}]", name);
        }

        public string BuildParameterName(string name)
        {
            return string.Format("@{0}", name);
        }

        public string BuildOriginalParameterName(string name)
        {
            return string.Format("@Original_{0}", name);
        }

        public string DatabaseType { get; set; }

        public string DatabaseVersion { get; set; }

        #endregion

        private string DatabaseVersionSqlString
        {
            get
            {
                return @"SELECT @@VERSION";
                //@"SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')";
            }
        }

        private string ColumnObjectInfoSqlString
        {
            get
            {
                return @"SELECT TC.CONSTRAINT_SCHEMA AS SCHEMA_NAME
,TC.CONSTRAINT_NAME
,TC.TABLE_NAME,TC.CONSTRAINT_TYPE
,FKCU.COLUMN_NAME,FKCU.ORDINAL_POSITION
,PKCU.CONSTRAINT_SCHEMA AS REF_SCHEMA_NAME,PKCU.TABLE_NAME AS REF_TABLE_NAME,PKCU.COLUMN_NAME AS REF_COLUMN_NAME
INTO #TEMP_COLUMN_INFO_VIEW	
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
	LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE FKCU
	ON FKCU.CONSTRAINT_NAME=TC.CONSTRAINT_NAME
	LEFT JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
	ON RC.CONSTRAINT_NAME=FKCU.CONSTRAINT_NAME
	LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE PKCU
	ON PKCU.CONSTRAINT_NAME=RC.UNIQUE_CONSTRAINT_NAME
	AND PKCU.ORDINAL_POSITION=FKCU.ORDINAL_POSITION
ORDER BY TC.TABLE_NAME,TC.CONSTRAINT_TYPE DESC,FKCU.ORDINAL_POSITION;

SELECT COL.TABLE_SCHEMA AS SchemaName
,SCH.SCHEMA_OWNER AS OwnerName
,CASE WHEN TBL.TABLE_TYPE='BASE TABLE' THEN 1 ELSE 2 END AS ObjectType
,COL.TABLE_NAME AS ObjectName
,(select top 1 value from ::fn_listextendedproperty(NULL, 'user', SCH.SCHEMA_OWNER, 'table', COL.TABLE_NAME, null, null) where name='MS_Description') as ObjectDescription
,COL.COLUMN_NAME AS ColumnName
,COL.ORDINAL_POSITION AS ColumnOrder
,COL.DATA_TYPE AS NativeType
,CASE WHEN COL.IS_NULLABLE='NO' THEN 0 ELSE 1 END AS AllowDBNull
,COL.CHARACTER_MAXIMUM_LENGTH AS Size
,COL.NUMERIC_PRECISION AS [Precision],COL.NUMERIC_SCALE AS Scale
,CASE WHEN COLUMNPROPERTY(OBJECT_ID(COL.TABLE_NAME), COL.COLUMN_NAME,'IsIdentity')='1' THEN 1 ELSE 0 END AS IsIdentity
,(select top 1 value from ::fn_listextendedproperty(NULL, 'user', SCH.SCHEMA_OWNER, 'table', COL.TABLE_NAME, 'column', COL.COLUMN_NAME) where name='MS_Description') as ColumnDescription
,PrimaryKeyName=(
	SELECT CONSTRAINT_NAME
	FROM #TEMP_COLUMN_INFO_VIEW
	WHERE CONSTRAINT_TYPE='PRIMARY KEY'
	AND SCHEMA_NAME=COL.TABLE_SCHEMA
	AND TABLE_NAME=COL.TABLE_NAME
	AND COLUMN_NAME=COL.COLUMN_NAME)
,PrimaryKeyOrder=(
	SELECT ORDINAL_POSITION
	FROM #TEMP_COLUMN_INFO_VIEW
	WHERE CONSTRAINT_TYPE='PRIMARY KEY'
	AND SCHEMA_NAME=COL.TABLE_SCHEMA
	AND TABLE_NAME=COL.TABLE_NAME
	AND COLUMN_NAME=COL.COLUMN_NAME)
,ForeignKeyName=(
	SELECT TOP 1 CONSTRAINT_NAME
	FROM #TEMP_COLUMN_INFO_VIEW
	WHERE CONSTRAINT_TYPE='FOREIGN KEY'
	AND SCHEMA_NAME=COL.TABLE_SCHEMA
	AND TABLE_NAME=COL.TABLE_NAME
	AND COLUMN_NAME=COL.COLUMN_NAME)
,ForeignKeySchemaName=(
	SELECT TOP 1 REF_SCHEMA_NAME
	FROM #TEMP_COLUMN_INFO_VIEW
	WHERE CONSTRAINT_TYPE='FOREIGN KEY'
	AND SCHEMA_NAME=COL.TABLE_SCHEMA
	AND TABLE_NAME=COL.TABLE_NAME
	AND COLUMN_NAME=COL.COLUMN_NAME)
,ForeignKeyTableName=(
	SELECT TOP 1 REF_TABLE_NAME
	FROM #TEMP_COLUMN_INFO_VIEW
	WHERE CONSTRAINT_TYPE='FOREIGN KEY'
	AND SCHEMA_NAME=COL.TABLE_SCHEMA
	AND TABLE_NAME=COL.TABLE_NAME
	AND COLUMN_NAME=COL.COLUMN_NAME)
,ForeignKeyColumnName=(
	SELECT TOP 1 REF_COLUMN_NAME
	FROM #TEMP_COLUMN_INFO_VIEW
	WHERE CONSTRAINT_TYPE='FOREIGN KEY'
	AND SCHEMA_NAME=COL.TABLE_SCHEMA
	AND TABLE_NAME=COL.TABLE_NAME
	AND COLUMN_NAME=COL.COLUMN_NAME)
,ForeignKeyColumnOrder=(
	SELECT TOP 1 ORDINAL_POSITION
	FROM #TEMP_COLUMN_INFO_VIEW
	WHERE CONSTRAINT_TYPE='FOREIGN KEY'
	AND SCHEMA_NAME=COL.TABLE_SCHEMA
	AND TABLE_NAME=COL.TABLE_NAME
	AND COLUMN_NAME=COL.COLUMN_NAME)
FROM INFORMATION_SCHEMA.COLUMNS COL
LEFT JOIN INFORMATION_SCHEMA.SCHEMATA SCH ON SCH.SCHEMA_NAME=COL.TABLE_SCHEMA
LEFT JOIN INFORMATION_SCHEMA.TABLES TBL ON TBL.TABLE_NAME=COL.TABLE_NAME
ORDER BY SchemaName,ObjectType,ObjectName,ColumnOrder;
DROP TABLE #TEMP_COLUMN_INFO_VIEW";
            }
        }

        private string ParameterObjectInfoSqlString
        {
            get
            {
                return @"SELECT PARA.SPECIFIC_SCHEMA AS SchemaName
,SCH.SCHEMA_OWNER AS OwnerName
,CASE WHEN ROUT.ROUTINE_TYPE='PROCEDURE' THEN 1 ELSE 2 END AS ObjectType
,PARA.SPECIFIC_NAME AS ObjectName
,ISNULL(PARA.PARAMETER_NAME,'ReturnParameter') AS ParameterName
,PARA.ORDINAL_POSITION AS ParameterOrder
,PARA.DATA_TYPE AS NativeType
,PARA.CHARACTER_MAXIMUM_LENGTH AS Size
,PARA.NUMERIC_PRECISION AS Precision,PARA.NUMERIC_SCALE AS Scale
,CASE 
	WHEN PARA.IS_RESULT='YES' THEN 6
	WHEN PARA.PARAMETER_MODE='IN' THEN 1 
	WHEN PARA.PARAMETER_MODE='OUT' THEN 2 
	WHEN PARA.PARAMETER_MODE='INOUT' THEN 3 
	ELSE 1 END AS Direction
,CASE WHEN PARA.IS_RESULT='NO' THEN 0 ELSE 1 END AS IsResult
FROM INFORMATION_SCHEMA.PARAMETERS PARA
LEFT JOIN INFORMATION_SCHEMA.SCHEMATA SCH ON SCH.SCHEMA_NAME=PARA.SPECIFIC_SCHEMA
LEFT JOIN INFORMATION_SCHEMA.ROUTINES ROUT ON ROUT.ROUTINE_SCHEMA=PARA.SPECIFIC_SCHEMA AND ROUT.ROUTINE_NAME=PARA.SPECIFIC_NAME
ORDER BY SchemaName,ObjectType,ObjectName, ParameterOrder";
            }
        }
    }
}
